// 
// Entify project
//  
// Author:
//       Lauri Taimila <lauri@taimila.com>
// 
// Copyright (c) 2010 Lauri Taimila
// 
// Permission is hereby granted, free of charge, to any person obtaining a copy
// of this software and associated documentation files (the "Software"), to deal
// in the Software without restriction, including without limitation the rights
// to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
// copies of the Software, and to permit persons to whom the Software is
// furnished to do so, subject to the following conditions:
// 
// The above copyright notice and this permission notice shall be included in
// all copies or substantial portions of the Software.
// 
// THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
// IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
// FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
// AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
// LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
// OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
// THE SOFTWARE.

using System;
using System.Linq;
using System.Data;
using System.Text;
using System.Reflection;
using System.Diagnostics;
using System.Collections.Generic;

using Mono.Data.Sqlite;

namespace Taimila.Entify.Sqlite
{
	/// <summary>
	/// Implements persistent store using SQLite database. PersistentStore's responsibility
	/// is to insert, update and delete data as requested. It trusts that the database
	/// structure exists for given entities and just handles the data access.
	/// </summary>
	internal class SqlitePersistentStore : IPersistentStore
	{	
		/// <summary>
		/// Database connection.
		/// </summary>
		private SqliteConnection connection;

		/// <summary>
		/// In-memory cache for entity counts.
		/// </summary>
		private Dictionary<Type, long> countCache;
		
		/// <summary>
		/// Creates new instance.
		/// </summary>
		public SqlitePersistentStore(SqliteConnection connection)
		{
			this.countCache = new Dictionary<Type, long>();
			this.connection = connection;
		}
		
		/// <summary>
		/// Adds entities into persistent store. Entities must not contain entities
		/// that already exists in this persistent store.
		/// </summary>
		/// <param name="entities">
		/// Entities to be added. 
		/// </param>
		public void Add<T>(T[] entities) where T : Entity
		{
			SqliteTransaction transaction = (SqliteTransaction) this.connection.BeginTransaction();
			
			try
			{
				this.InsertEntities(entities);
				this.InsertEntityRelations(entities);
				transaction.Commit();
			}
			catch
			{
				transaction.Rollback();
				throw;
			}

			this.UpdateCountCache(typeof(T), entities.Length);
		}

		/// <summary>
		/// Update existing entities in the store.
		/// </summary>
		/// <param name="entities">
		/// Entities to be updated. <see cref="System.Object"/>
		/// </param>
		public void Update<T>(T[] entities) where T : Entity
		{
			Type type = typeof(T);
			DateTime updateTime = DateTime.Now;
			int affectedRows = 0;
			
			SqliteTransaction transaction = (SqliteTransaction) this.connection.BeginTransaction();
			
			try
			{
				using (SqliteCommand command = (SqliteCommand) this.connection.CreateCommand())
				{
					foreach(var entity in entities)
					{
						command.CommandText = this.GetUpdateQueryForType(type) + 
											  this.GetDeleteQueriesForEntityRelations(type) + 
											  this.GetInsertQueriesForEntityRelations(entity);
						
						var allProperties = SqlitePersistentProperty.GetPersistentPropertiesWithValues(entity);
						var properties = from p in allProperties where !p.IsManyRelation select p;
						
						foreach(var p in properties)
						{
							command.Parameters.AddWithValue(p.SqliteParameterName, p.SqliteValue);
						}
						
						command.Parameters.AddWithValue("@updateCheck", entity.GetLastModifiedDateAsFileTime());
						command.Parameters.AddWithValue("@lastUpdate", updateTime.ToFileTime());
						command.Parameters.AddWithValue("@entityId", entity.GetEntityId());
						
						EntifyLogger.LogQuery(command.GetQuery());
						affectedRows = command.ExecuteNonQuery();
						command.Parameters.Clear();
							
						if(affectedRows == 0)
						{
							throw new UpdateConflictException("Entity version doesn't match. This usually means that the other thread has updated the same entity.");	
						}
					}
				}
				
				transaction.Commit();

				foreach(var entity in entities)
				{
					entity.SetLastModifiedDate(updateTime);
				}
			}
			catch
			{
				transaction.Rollback();
				throw;
			}
		}
		
		/// <summary>
		/// Remove existing persistent entities from store.
		/// </summary>
		/// <param name="entities">
		/// Entities to be removed.
		/// </param>
		public void Remove<T>(T[] entities) where T : Entity
		{			
			Type type = typeof(T);
			int affectedRows = 0;
			
			SqliteTransaction transaction = (SqliteTransaction) this.connection.BeginTransaction();
			
			try
			{
				using(SqliteCommand command = (SqliteCommand) this.connection.CreateCommand())
				{	
					command.CommandText = this.GetDeleteQueryForType(type) + this.GetDeleteQueriesForEntityRelations(type);
					
					foreach(var entity in entities)
					{
						command.Parameters.AddWithValue("@updateCheck", entity.GetLastModifiedDateAsFileTime());
						command.Parameters.AddWithValue("@entityId", entity.GetEntityId());
						
						EntifyLogger.LogQuery(command.GetQuery());
						affectedRows = command.ExecuteNonQuery();
						command.Parameters.Clear();
						
						if(affectedRows == 0)
						{
							throw new UpdateConflictException("Entity version doesn't match. This usually means that the other thread has updated the same entity.");	
						}
					}
				}
				
				transaction.Commit();
			}
			catch
			{
				transaction.Rollback();
				throw;
			}
			
			this.UpdateCountCache(type, entities.Length * -1);
		}
					
		/// <summary>
		/// Get entities with entity request.
		/// </summary>
		/// <param name="entityRequest">
		/// Entity request. <see cref="EntityRequest<T>"/>
		/// </param>
		/// <returns>
		/// All found entities. 
		/// </returns>
		public T[] Get<T>(EntityRequest<T> entityRequest) where T : Entity
		{
			Dictionary<long, T> entities = new Dictionary<long, T>();

			using(SqliteCommand command = entityRequest.GetCommand())
			{	
				command.Connection = this.connection;
				EntifyLogger.LogQuery(command.GetQuery());
				
				using(SqliteDataReader reader = command.ExecuteReader())
				{
					while(reader.Read())
					{
						T entity = reader.GetEntity<T>();
						long id = (long) entity.GetEntityId();
						entities.Add(id, entity);
					}	
				}
			}
			
			if(entities.Count > 0)
			{
				this.SetLazyLoadIdentifiers<T>(entities);
			}
			
			return entities.Values.ToArray();
		}
		
		/// <summary>
		/// Get number of entities of given type in persistent store.
		/// </summary>
		/// <returns>
		/// Entity count of given type. <see cref="System.Int32"/>
		/// </returns>
		public long GetEntityCount<T>() where T : Entity
		{
			Type type = typeof(T);
			long count = 0;
			
			// Check the in-memory cache before getting the count from Sqlite database.
			if(this.countCache.ContainsKey(type))
			{
				count = this.countCache[type];
			}
			else
			{
				using(SqliteCommand command = (SqliteCommand) this.connection.CreateCommand())
				{
					command.CommandText = "SELECT COUNT(*) AS Count FROM [" + type.FullName + "]";
					EntifyLogger.LogQuery(command.GetQuery());
					
					using(SqliteDataReader reader = command.ExecuteReader())
					{
						reader.Read();
						count = reader.GetInt64("Count");
					}
				}
				
				// Add to in-memory count cache
				this.countCache[type] = count;
			}
			
			return count;
		}
		
		/// <summary>
		/// Creates insert query for given type.
		/// </summary>
		/// <param name="type">
		/// Type that query is created for. <see cref="Type"/>
		/// </param>
		/// <returns>
		/// Insert SQL Query. <see cref="System.String"/>
		/// </returns>
		private string GetInsertQueryForType(Type type)
		{
			var properties = SqlitePersistentProperty.GetPersistentProperties(type);
			var typeProperties = from p in properties where !p.IsManyRelation select p;
			
			List<string> columns = (from p in typeProperties select p.PersistentName).ToList();
			List<string> parameters = (from p in typeProperties select p.SqliteParameterName).ToList();
			
			// Add special fields and values for them.
			//columns.Add("[Entity Id]");
			columns.Add("[Entity Version]");
			columns.Add("[Entity Last Update]");
			//parameters.Add("@entityId");
			parameters.Add(AttributeReader.GetEntityVersion(type).ToString());
			parameters.Add("@lastUpdate");
			
			StringBuilder query = new StringBuilder();
			query.AppendLine("INSERT INTO [" + type.FullName + "]");
			query.AppendLine("(");                 
			query.AppendLine("\t" + string.Join(",\n\t", columns.ToArray()));
			query.AppendLine(")\nVALUES\n(");
			query.AppendLine("\t" + string.Join(",\n\t", parameters.ToArray()));
			query.AppendLine(");");
			query.AppendLine("SELECT last_insert_rowid() AS Id;");
			
			return query.ToString();
		}
		
		/// <summary>
		/// Get insert queries for many-to-many relations of the specified entity.
		/// </summary>
		/// <param name="entity">
		/// Specified entity. <see cref="Entity"/>
		/// </param>
		/// <returns>
		/// SQL Insert queries. <see cref="System.String"/>
		/// </returns>
		private string GetInsertQueriesForEntityRelations(Entity entity)
		{
			long? entityId = entity.GetEntityId();
			Type type = entity.GetType();
			StringBuilder query = new StringBuilder();
			
			var relations = from p in type.GetProperties() 
							where AttributeReader.HasEntityProperty(p) && AttributeReader.IsManyRelationship(p) 
							select p;
			
			foreach(PropertyInfo relation in relations)
			{	
				foreach(var relationId in entity.GetRelationIdentifiersOf(relation))
				{
					query.AppendLine("INSERT INTO [" + type.FullName + "." + AttributeReader.GetPersistentName(relation) + "]");
					query.AppendLine("(");
					query.AppendLine("\t[Source Id], [Destination Id]");
					query.AppendLine(")\nVALUES\n(");
					query.AppendLine("\t" + entityId + ", " + relationId);
					query.AppendLine(");");
				}
			}

			return query.ToString();
		}
		
		/// <summary>
		/// Creates delete query for given type.
		/// </summary>
		/// <param name="type">
		/// Type that query is created for. <see cref="Type"/>
		/// </param>
		/// <returns>
		/// Delete SQL Query. <see cref="System.String"/>
		/// </returns>
		private string GetDeleteQueryForType(Type type)
		{
			return "DELETE FROM [" + type.FullName + "] WHERE [Entity Id]=@entityId AND [Entity Last Update]=@updateCheck;";
		}
		
		/// <summary>
		/// Get delete queries for many-to-many relations of the specified entity type.
		/// </summary>
		/// <param name="type">
		/// Type of the entity. <see cref="System.Type"/>
		/// </param>
		/// <returns>
		/// SQL delete queries. <see cref="System.String"/>
		/// </returns>
		private string GetDeleteQueriesForEntityRelations(Type type)
		{
			var properties = SqlitePersistentProperty.GetPersistentProperties(type);
			var manyRelations = from p in properties where p.IsManyRelation select p;
			
			StringBuilder query = new StringBuilder();
			
			foreach(var relation in manyRelations)
			{
				query.AppendLine("DELETE FROM [" + type.FullName + "." + relation.PersistentName	 + "] WHERE [Source Id]=@entityId;");
			}
			
			return query.ToString();
		}
		
		/// <summary>
		/// Creates update query for given type.
		/// </summary>
		/// <param name="type">
		/// Type that query is created for. <see cref="Type"/>
		/// </param>
		/// <returns>
		/// Update SQL Query. <see cref="System.String"/>
		/// </returns>
		private string GetUpdateQueryForType(Type type)
		{
			var allProperties = SqlitePersistentProperty.GetPersistentProperties(type);
			var properties = from p in allProperties where !p.IsManyRelation select p;
			
			List<string> columns = (from p in properties select p.PersistentName + "=" + p.SqliteParameterName).ToList();
			
			columns.Add("[Entity Version]=" + AttributeReader.GetEntityVersion(type));
			columns.Add("[Entity Last Update]=@lastUpdate");
			
			StringBuilder query = new StringBuilder();
			query.AppendLine("UPDATE [" + type.FullName + "] SET");
			query.AppendLine("\t" + string.Join(",\n\t", columns.ToArray()));
			query.AppendLine("WHERE [Entity Id]=@entityId AND [Entity Last Update]=@updateCheck;");
			
			return query.ToString();
		}
		
		/// <summary>
		/// Insert specified entities to persistent store. This method inserts
		/// only the entities them selves, not the many-to-many relations.
		/// </summary>
		/// <param name="entities">
		/// Entities to be inserted. 
		/// </param>
		private void InsertEntities<T>(T[] entities) where T : Entity
		{
			Type type = typeof(T);
			DateTime updateTime = DateTime.Now;
			
			using(SqliteCommand command = (SqliteCommand) this.connection.CreateCommand())
			{	
				command.CommandText = this.GetInsertQueryForType(type);

				foreach(var entity in entities)
				{	
					var allProperties = SqlitePersistentProperty.GetPersistentPropertiesWithValues(entity);
					var properites = from p in allProperties where !p.IsManyRelation select p;
					
					foreach(var p in properites)
					{
						command.Parameters.AddWithValue(p.SqliteParameterName, p.SqliteValue);
					}
					
					command.Parameters.AddWithValue("@lastUpdate", updateTime.ToFileTime());
					EntifyLogger.LogQuery(command.GetQuery());
	
					try
					{
						long id = (long) command.ExecuteScalar();
						entity.SetEntityId(id);
						entity.SetLastModifiedDate(updateTime);
					}
					catch(SqliteException e)
					{
						throw new AlreadyExistsException("Entity already exists in persistent store.", e);	
					}
					
					command.Parameters.Clear();
				}
			}
		}
		
		/// <summary>
		/// Inserts many-to-many relation information of the specified entities.
		/// </summary>
		/// <param name="entities">
		/// Specified entities. <see cref="Entity[]"/>
		/// </param>
		private void InsertEntityRelations(Entity[] entities)
		{
			using(SqliteCommand command = (SqliteCommand) this.connection.CreateCommand())
			{
				foreach(var entity in entities)
				{
					string query = this.GetInsertQueriesForEntityRelations(entity);
					
					if(!string.IsNullOrEmpty(query))
					{
						command.CommandText = query;
						EntifyLogger.LogQuery(command.GetQuery());
						command.ExecuteNonQuery();
					}
				}
			}
		}
		
		/// <summary>
		/// Set lazy load identifiers for given entities. Lazy load identifiers are fetched
		/// from the persistent store database relation tables.
		/// </summary>
		/// <param name="entities">
		/// Specified entities. <see cref="Dictionary<System.Int64, T>"/>
		/// </param>
		private void SetLazyLoadIdentifiers<T>(Dictionary<long, T> entities) where T : Entity
		{
			Type type = typeof(T);
		
			var entityIdentifiers = from e in entities.Values select e.GetEntityId().ToString();
			string identifiers = string.Join(", ", entityIdentifiers.ToArray());
			var manyRelations = from p in type.GetProperties() where AttributeReader.IsManyRelationship(p) select p;

			foreach(var relation in manyRelations)
			{
				string query = "SELECT * FROM [" + type.FullName + "." + relation.Name + "] WHERE [Source Id] IN (" + identifiers + ") ORDER BY [Source Id] ASC";
				
				using(SqliteCommand command = (SqliteCommand) this.connection.CreateCommand())
				{	
					command.Connection = this.connection;
					command.CommandText = query;
					
					using(SqliteDataReader reader = command.ExecuteReader())
					{
						List<long?> relationIdentifiers = new List<long?>();
						long source = (long) (from e in entities.Values select e.GetEntityId()).Min();

						while(reader.Read())
						{
							long sourceId = reader.GetInt64("Source Id");
							long relationId = reader.GetInt64("Destination Id");
							
							if(sourceId == source)
							{
								relationIdentifiers.Add(	relationId);
							}
							else
							{
								entities[source].SetLazyLoadIdentifiers(relation, relationIdentifiers);
								relationIdentifiers.Clear();
								source = sourceId;
								relationIdentifiers.Add(relationId);
							}
						}
						
						entities[source].SetLazyLoadIdentifiers(relation, relationIdentifiers);
					}
				}
			}
		}
		
		/// <summary>
		/// Updates in-memory count cache. Count cache contains counts
		/// for each entity type.
		/// </summary>
		/// <param name="type">
		/// Type which count will be updated. <see cref="Type"/>
		/// </param>
		/// <param name="difference">
		/// Differnce to last value. This should be number of inserted or deleted entities. 
		/// If entities were deleted then value must be negative. <see cref="System.Int32"/>
		/// </param>
		private void UpdateCountCache(Type type, int difference)
		{
			if(this.countCache.ContainsKey(type))
			{
				this.countCache[type] = this.countCache[type] + difference;
			}
		}
	}
}
